﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Customer_Track_GetList_Item]
(
	@CusId VARCHAR(50),
	@StartIndex int,
	@EndIndex int,
	@UserId VARCHAR(30)
)
AS
BEGIN
	Declare @CompanyId int,@ProtectId int,@ProtectName nvarchar(30),@ProtectDate datetime
	Declare @CusName nvarchar(100),@ProtectUser varchar(30),@ServiceUser varchar(30),@BDServiceUser varchar(30),@UserName nvarchar(30),@UpTime datetime
	If @UserId <> ''
	Begin
		Select @CompanyId=CompanyId,
		@CusName=CustomerName,
		@UserName=IsNull((Select Top 1 EName From Employee Where UserName=@UserId),''),
		@ProtectId=IsNull(ProtectId,0),
		@ProtectUser=IsNull(ProtectName,''),
		@ServiceUser=IsNull(serviceperson,''),--维护客服
		@BDServiceUser=IsNull(BDserviceperson,''),--百度客服
		@ProtectName=IsNull((Select Top 1 EName From Employee Where UserName=ProtectName),''),
		@ProtectDate=IsNull(ProtectDate,'1900-01-01'),
		@UpTime=IsNull(UpTime,GetDate())
		From Customer Where CustomerId=@CusId

		--记录哈尔滨、大连、山西的客户，非本人查看跟踪记录的情况
		If @CompanyId In(1, 22,50) And @ProtectUser<>@UserId And @ServiceUser<>@UserId And @BDServiceUser<>@UserId
			Begin
				Declare @tmpUpTime datetime
--				Set @tmpUpTime = IsNull(Select Top 1 UpTime From Customer_View_Log Where CusName=@CusName And UserName=@UserName Order By UpTime Desc,'1900-01-01')
				Select Top 1 @tmpUpTime = IsNull(UpTime,'1900-01-01') From Customer_View_Log Where CusName=@CusName And UserName=@UserName Order By UpTime Desc
				If DateDiff(n,@tmpUpTime,Getdate())>30	--该员工最后1次浏览此客户已超过30分钟
				Begin
					If @ProtectId=1 Or @ProtectId=3	--(公库、已签单)
						Begin
							If DateDiff(n,@UpTime,Getdate())>60	--距最后的批注日期已超过1小时
								Insert Into Customer_View_Log(CusId,CusName,UserId,UserName,ProtectId,ProtectUser,ProtectName,ProtectDate,CompanyId)
								Values(@CusId,@CusName,@UserId,@UserName,@ProtectId,@ProtectUser,@ProtectName,@UpTime,@CompanyId)
						End
					Else	--保护、跟踪(2,4)
						Insert Into Customer_View_Log(CusId,CusName,UserId,UserName,ProtectId,ProtectUser,ProtectName,ProtectDate,CompanyId)
						Values(@CusId,@CusName,@UserId,@UserName,@ProtectId,@ProtectUser,@ProtectName,@ProtectDate,@CompanyId)
				End
			End
	End
;
	;WITH List As(Select ROW_NUMBER() OVER (Order By id Desc) AS Row,
		id,
		CustomerId, 
		(SELECT Customer.CustomerName  FROM Customer WHERE Customer.CustomerId=Customer_Track.CustomerId) AS CustomerName,
		IntentionsId, 
		PublicityDemandId, 
		NetworkConsciousnessId,
		GenjinId, 
		IsNull(Record,'') As Record, 
		UpName, 
		UpTime, 
		DepartmentId, 
		CompanyId,
		Case When UpName='' Then '...' Else	IsNull((Select Top 1 EName From Employee Where UserName=Customer_Track.UpName),'...') End As EName,
		IsNull((Select Title From Customer_Intentions Where Id=Customer_Track.IntentionsId),'...') As Intentions,
		IsNull((Select Phases From Customer_PublicityDemand Where Id=Customer_Track.PublicityDemandId),'...') As PublicityDemand,
		IsNull((Select Title From Customer_NetworkConsciousness Where Id=Customer_Track.NetworkConsciousnessId),'...') As NetworkConsciousness,
		IsNull((Select Title From Customer_Genjin Where Id=Customer_Track.GenjinId),'...') As Genjin
		From Customer_Track
	             WHERE CustomerId=@CusId
	)
	Select *,(Select Count(0) From list) As RecordCount From list Where Row Between @StartIndex and @EndIndex  Order By Row
END
